Cream of the Crop 3/Cream of the Crop 3.iso/utility/ascto123.zip/ASCTO123.DOC

Text File | 1993-12-10 | 26KB | 586 lines

INTRODUCTION

The ASCII To 1-2-3 File Translator program (ASCTO123.EXE) converts fixed
field ASCII text files (such as flat file database files) into Lotus 1-2-3
WK1 format. This program removes the need to use the Lotus /File Import and
/Data Parse commands to import such files into Lotus 1-2-3, and overcomes
several limitations associated with using those commands.

Using ASCTO123 you can import files with records of up to 32,000 characters
in length.  With the /File Import command, you are limited to between 240
and 512 characters, depending on what version of 1-2-3 or Symphony you are
using.  Using ASCTO123, each field in your file is converted to a cell in
Lotus 1-2-3, properly formatted according to it's data type (number, date,
time or label).  Using the Lotus /File Import command converts each record
of your file to a long label in a single cell in 1-2-3.  You must then use
the /Data Parse command to split the long label into separate cells of the
right data type.  There is very little support for directly converting date
and time fields into 1-2-3 date and time values.

LIMITATIONS

This program has been extensively tested, but it is impossible to test all
possible uses of the program, and errors in conversion can occur.  Please
check your spreadsheet for accuracy after using this product.  In no event
will Strider Software be liable for any damages, including loss of data,
lost profits, or any other incidental or consequential damages arising from
the use of this product.

LIMITED LICENSE

This software is distributed on a "shareware" basis.  It is not free
software, or in the public domain.  You are granted a license to use this
copy of ASCTO123 for a free 30 day evaluation period.  If you continue to
use ASCTO123 after the evaluation period, you must send a registration fee
of $25 to Strider Software to obtain a perpetual single-user license.  For
your fee you will also be sent a diskette with the latest version of the
program, and free upgrade to the next release when available.

You may give this program to others provided you follow these restrictions:
The program file (ASCTO123.EXE) is not modified in any way, the
documentation file is included (ASCTO123.DOC), and you disclose the legal
requirement to register the software for use beyond the 30 day evaluation
period.  This software program is owned by Strider Software and is licensed
for use to registered licensees.  You may not sell the program, but you may
charge a modest fee to distribute the program on diskettes or electronic
bulletin boards.

ACKNOWLEDGMENTS

Lotus 1-2-3 is a registered trademark of Lotus Development Corporation.

USING ASCTO123

ASCTO123 is a command line program which is run from the DOS prompt.  It is
suitable for use interactively or in a batch file.  For a full description
of ASCTO123 command line arguments and optional switches, refer to the
section entitled ASCTO123 REFERENCE.  The rest of this section gives a
tutorial on the use of ASCTO123.

Assume you have an ASCII data file (SAMPLE1.DAT), which looks like this:

05/11/6211:15:30AMHANLON    DAVID      10.50
01/06/6003:12:55PMVEITCH    HEATHER     8.75

This file has 5 fields: a birth date, birth time, surname, given name and
weight (number).  To translate this file into Lotus 1-2-3 format you first
create (using a text editor) an ASCII file (SAMPLE1.LYT) which describes
the layout of the fields in SAMPLE1.DAT.  The layout file would look like
this:

BIRTHD,8,Dd/m/y
BIRTHT,10,Th:m:sAM
SNAME,10,L
GNAME,10,L
WEIGHT,6,N2

Each line of the layout file describes one field from the input (SAMPLE1.DAT)
file.

Let's look at the first line of the layout file.

BIRTHD,8,Dd/m/y

There are 3 items in this line, each item delimited with commas.  The first
item is the field name, BIRTHD.  The second item, 8, is the number of
characters for the BIRTHD date field.  The last item, Dd/m/y, describes
the format of the field.  The first character, 'D', identifies this field as
a date field.  The remaining characters, 'd/m/y', specify the location of
the day, month and year within the field.

Now look at the second line of the layout file.

BIRTHT,10,Th:m:sAM

The first 2 items are the field name and field length, as before.  The last
item, Th:m:sAM, describes the format of the field.  As with the date field,
the first character, 'T', identifies this field as a time field.  The
remaining characters, 'h:m:sAM', specify the location of the hour, minute
and second values within the field, and whether an AM/PM indicator is
present in the field.

Now, the third line of the layout file.

SNAME,10,L

The first 2 items are the field name and field length, as before.  The last
item, 'L', identifies this field as a label, or alphanumeric field.

The fourth line of the layout file is similar to line three, so let's skip
to the fifth line.

WEIGHT,6,N2

Again, the first 2 items are the field name and length.  The last item,
'N2', identifies this field as a numeric field with 2 decimal places.

We can now translate this file to 1-2-3 format as follows:

ASCTO123 SAMPLE1.DAT SAMPLE1.LYT

This creates a 1-2-3 file, SAMPLE1.WK1, which looks like this in Lotus 1-2-3:

    A        B        C        D        E
1    BIRTHD        BIRTHT        SNAME        GNAME        WEIGHT
2    05-Nov-62    11:15:30 AM    HANLON        DAVID        10.50
3    01-Jun-60    03:12:55 PM    VEITCH        HEATHER         8.75

Note that the first row of this spreadsheet contains the field names from
the layout file.  The birth dates have been converted to Lotus 1-2-3 date
values, and have been formatted in Lotus Date format 1.  The birth times
have been converted to Lotus 1-2-3 time values, and have been formatted in
Lotus Time format 1.  The surname and given names have been converted to
left-aligned labels.  The weight field has been converted to a number,
formatted as fixed with 2 decimal places.  Also note that the column widths
have been adjusted to be wide enough to display the field values and titles.

MORE ON LABELS

There's not much to labels in Lotus 1-2-3.  What you see in the input
(ASCII) file is what you see in the Lotus 1-2-3 file.  You can optionally
choose to change the default alignment from left-aligned to either right or
center-aligned, by using the command line switch /fl.  Use /flr for
right-aligned labels, and /flc for center-aligned labels.  (Refer to the
section 'ASCTO123 REFERENCE' for full details on available command line
options).

For example, if we recreate the SAMPLE1.WK1 file as follows:

ASCTO123 SAMPLE1.DAT SAMPLE1.LYT /flc

then the surname and given names (and all the field titles) will be
converted to center-aligned labels.

MORE ON NUMBERS

The weight field in our sample file contained explicit decimal points.  It
is not uncommon, however, for such a field to be stored without the decimal
point, as follows: (This is SAMPLE2.DAT)

1962-11-0511:15AMHANLON    DAVID      1050
1960-06-0103:13PMVEITCH    HEATHER     875

In this file the decimal point is implied.  ASCTO123 knows to expect 2
decimal places in this field, because the format descriptor is N2.
ASCTO123 will therefore insert a decimal point in the right location for
this field.  That is, 1050 will be converted to 10.50, and 875 will be
converted to 8.75.

Numeric values are formatted as fixed by default.  You can change the
default format for numbers by using the command line switch /fn as shown
below:

Switch        Format

/fnf        Fixed
/fnc        Currency
/fnp        Puncuated (comma)
/fns        Scientific
/fn%        Percent

For example, to format numeric values as currency, we do as follows:

ASCTO123 SAMPLE2.DAT SAMPLE2.LYT /fnc

MORE ON DATE VALUES

Date values present a few problems when converting to Lotus 1-2-3.  That's
because a date is actually three different pieces of data tied together; the
day, month and year.  There is no standard way to specify these 3 pieces
when you are describing a date, which causes most of the problems.  ASCTO123
lets you describe the format of your date fields with a 'picture', using the
symbols 'd' (for 2 digit day value), 'm' (for 2 digit month value), 'y' (for
2 digit year value) and 'yy' (for 4 digit year value).

For example, the date 'picture' m*d*yy tells ASCTO123 that your date field
is 10 characters in length, and that the month starts in position 1, the day
in position 4 and the (4 digit) year starts in position 7.  The day, month
and year values are separated by asterisk (*) characters.

For example, suppose you had a file with a date field containing dates of
the form 31-12-1993. You would describe this date field in the layout file
as follows:

DATEFIELD,10,Dd-m-yy

Another common way to represent date values in a file is to omit the day
value or the year value.  For example, you might have a file containing a
list of your appointments for 1993.  Such a file might have a field for the
date of each appointment, as follows (SAMPLE3.DAT)

03/25Dentist     03:00PM
03/27Doctor      09:30AM
03/28Dinner      08:00PM

The year is not stored in the file, but is implied from the context.  i.e.
1993 appointments.  The date field would be described in the layout file
(SAMPLE3.LYT) as follows

APPTDATE,5,Dm/d

When a date which is missing a year value is converted into Lotus, ASCTO123
will, by default, use the current year to complete the date value.  You can
specify a different year by using the command line switch /dy#, where # is
an integer from 0 (representing 1900) to 199 (representing 2099).

If a date field contains only month and year values, then ASCTO123 will, by
default, use a day value of 1 to complete the date value.  You can specify
a different day to use by using the command line switch /dd#, where # is an
integer from 1 to 28.

Another difficulty with dates occurs when there are only 2 digits for the
year value.  For example, supposing you had a file with date values of the
form

31/12/00

Does this date represent 31-Dec-1900 or 31-Dec-2000?  By default, ASCTO123
interprets such a date as 31-Dec-1900.  By using the command line switch
/c#, where # is an integer from 0 to 99, you can modify this behaviour.
For example, using the commmand line switch /c10 causes ASCTO123 to treat
all dates with a 2 digit year value less than 10 as being in the 21st
century, and not the 20th century.

For example, using the command line switch /c10, causes the date value
31/12/09 to be converted to 31-Dec-2009, while 31/12/10 is converted to
31-Dec-1910.

MORE ON TIME VALUES

Time values present the same problems as date values when converting to
Lotus 1-2-3.  A time value is also composed of three different pieces of
data, the hour, minute and second, as well as the optional AM/PM indicator.
As with date values,  ASCTO123 lets you describe the format of your time
fields with a 'picture', using the symbols 'h' (for 2 digit hour value),
'm' (for 2 digit minute value), 's' (for 2 digit second value) and 'AM' or
'PM' to indicate the presence of the AM/PM indicator.

For example, the time 'picture' h:m:s tells ASCTO123 that your time field
is 8 characters in length, and that the hour starts in position 1, the
minute in position 4 and the second starts in position 7.  There is no
AM/PM indicator, meaning the time field is in 24 hour format.  i.e. 3:00PM
would be represented by an hour value of 15 (12 + 3).  The hour, minute
and second values are separated by colon (:) characters.

For example, suppose you had a file with a time field containing time values
of the form 08:30:10PM. You would describe this time field in the layout
file as follows:

TIMEFIELD,10,Th:m:sAM

Another common way to represent time values in a file is to omit the second
value.  For example, you might have a file containing a list of your
appointments for 1993.  Such a file might have a field for the time of each
appointment, as follows (SAMPLE3.DAT)

03/25Dentist     03:00PM
03/27Doctor      09:30AM
03/28Dinner      08:00PM

The second value is not stored in the file.  The time field would be
described in the layout file (SAMPLE3.LYT) as follows

APPTTIME,7,Th:mAM

When a time which is missing a second value is converted into Lotus, ASCTO123
will use 0 for the second value.  An option to specify a different default
second value has not been included, because the author could not envision a
need for it.  If you feel otherwise, please send me a note to that effect.

INVALID DATA

The default action for ASCTO123 when it encounters an invalid value in the
input file is to continue execution.  An @ERR value is written to the cell
containing the invalid data. This is appropriate in most cases since invalid
data occurs quite frequently in typical data files. However, if the layout file
has been incorrectly set up, (for instance, the length for one of the fields
might be incorrect), ASCTO123 will likely create a WK1 file will mostly
@ERR values. To avoid this, use the /et command line switch which will
cause ASCTO123 to terminate execution on encountering an invalid data value.
Once the layout file has been correctly specified you can run ASCTO123
without the /et command line option to produce your final WK1 file.

If you have a file layout which specifies the starting and ending columns
for each field of the input file, it can sometimes be difficult to properly set
up the layout file as required by ASCTO123. Once you find that the layout
file has not been set up properly, it can be frustrating and time consuming
to find the incorrect entry. Typically, an error in the field length for one of the
fields is the culprit. To assist you in locating errors such as this, ASCTO123
can (optionally) create a layout file which specifies starting and ending column
numbers for each field in the file. Use this file to compare with your existing
layout file description to quickly find the incorrect field length value.

Use the /l command line switch to create a layout file which describes the
starting and ending column numbers for each field. ASCTO123 will create this
file in the current directory, and will have a (unique) name of the form TMP#.$$$.
# is a digit from 1 to 65536, which guarantees that the name will be unique.

Remember to erase all files of the form TMP#.$$$ after you are finished setting
up the layout file.

MORE OPTIONS

ASCTO123 gives you the ability to select which fields of your input file you
would like to include in the translated 1-2-3 WK1 file, and also the ability
to specify the order of the fields in the 1-2-3 file.  By default, all
fields are included in the 1-2-3 file, in the order in which they occur in
the input file.  You can change this by specifying 2 more items in the
layout file.   Recall that we had 3 items for each field in the layout file:
the field name, length, and a format descriptor for the field.  These are
required items for every field in the input file.  The fourth and fifth items
specify whether to include the field in the 1-2-3 file, and the numeric
location (order) of the field in the 1-2-3 file, from left to right.

For example, recall the input file SAMPLE1.DAT

05/11/6211:15:30AMHANLON    DAVID      10.50
01/06/6003:12:55PMVEITCH    HEATHER     8.75

Suppose we are interested in just the birth date and surname fields of this
file, and that we really want to have a 1-2-3 file with surname and then
birth date, in that order.  We can achieve this by setting up our layout
file (SAMPLE1O.LYT) as follows:

BIRTHD,8,Dd/m/y,Y,2
BIRTHT,10,Th:m:sAM,N
SNAME,10,L,Y,1
GNAME,10,L,N
WEIGHT,6,N2,N

Notice that the fourth item in each line is either a 'Y' (to include the
field), or a 'N' (to exclude the field).  For the BIRTHD and SNAME fields we
also include a number as the fifth item, which specifies the order that these
fields should occur in the 1-2-3 file.

Now run ASCTO123 as follows:

ASCTO123 SAMPLE1.DAT SAMPLE1O.LYT /wSAMPLE1O.WK1

to create SAMPLE1O.WK1 (note the /w command line switch so that we don't
overwrite SAMPLE1.WK1), which will look like this:

      A           B
1      SNAME       BIRTHD
2      HANLON     05-Nov-62
3      VEITCH     01-Jun-60

ASCTO123 REFERENCE

The formal syntax for using ASCTO123 is:

ASCTO123 AsciiFile LayoutFile [/Option1 /Option2 ...]

Where,

AsciiFile is the name of the (ASCII) input file to be translated into WK1
format, and LayoutFile is the name of the (ASCII) file which contains field
descriptions for AsciiFile.

The available command line switches are listed below:

/c#, where # is an integer from 0 to 99.  Causes ASCTO123 to add 100 to year
values in date fields which are less than #.  This is useful when your file
has 2 digits for the year value, and low numbers (such as 0), should be
interpreted as being in the 21st century instead of the early 20th century.
By default, ASCTO123 will interpret a 2 digit year value of 0 as the year
1900.

/dd#, where # is an integer from 1 to 28.  Causes ASCTO123 to use # for the
day value in date fields where only the month and year are specified in the
layout file.  By default, ASCTO123 uses 1 for the day value.

/dy#, where # is an integer from 0 to 199.  Causes ASCTO123 to use # for the
year value in date fields where only the day and month are specified in the
layout file.  0 corresponds to the year 1900.  By default ASCTO123 uses the
current year as specified by the computer's system clock.

/ex, where x is one of {n,t}.  Controls how ASCTO123 behaves when it
encounters invalid data values in the input file.  'n' causes ASCTO123 to
ignore invalid values and continue processing. 't' causes ASCTO123 to
terminate processing the input file.  By default, ASCTO123 will continue
processing when it encounters an invalid value in the input file and write
an @ERR value to the WK1 file.

/fdx, where x is one of {1,2,3}.  Causes ASCTO123 to format date fields
using Lotus date format 1, 2 or 3, resp.  By default, ASCTO123 will use
Lotus date format 1 (dd-Mon-yy).  Lotus date format 2 is dd-Mon, while format
3 is Mon-yy.

/flx, where x is one of {l,r,c}.  Causes ASCTO123 to format labels as
left-aligned, right-aligned or center-aligned.  By default, ASCTO123 will
use left-alignment.

/fnx, where x is one of {f,c,p,s,%}.  Causes ASCTO123 to format numeric
values as 'fixed', 'currency', 'punctuated' (comma), 'scientific' or
'percent', resp.  By default, ASCTO123 will use 'fixed' format for numeric
values.

/ftx, where x is one of {1,2}.  Causes ASCTO123 to format time fields using
Lotus time format 1 or 2, resp.  By default, ASCTO123 will use Lotus time
format 1 (hh:mm:ss AM).  Format 2 is hh:mm AM.

/h, causes ASCTO123 to display a 'help' screen.  This is useful when you
don't remember how to use the command line switches, and don't want to have
to refer to the documentation.  You also get this screen by using /?, or by
just typing ASCTO123 with no parameters.

/l, causes ASCTO123 to create a file (in the current directory, and with the
name TMP#.$$$, where # is a digit from 1 to 65536) which contains the
starting and ending column numbers for each field in the input file, based on
the entries in the layout file provided. This can be useful in locating errors
in the layout file when you are working from a description of the file in terms
of starting and ending column numbers, and not field lengths as is required by
ASCTO123. You should remember to erase all files of the form TMP#.$$$ after
you are finished setting up for ASCTO123.

/tx, where x is one of {+,-}.  '+' causes ASCTO123 to include a row in the
1-2-3 file, containing the field names from the layout file.  This provides
an instant 'database' in your 1-2-3 file.  '-' prevents ASCTO123 from
creating this row of column headings in the 1-2-3 file.  By default,
ASCTO123 will create the row of column headings.

/vx, where x is one of {+,-}.  '+' causes ASCTO123 to display status and
error messages on the screen while it is running.  '-' disables all messages
to the screen (even error messages).  By default, ASCTO123 will display
status and error messages on the screen.  You should only disable this if
you are running ASCTO123 from a batch file, and only then if you are
checking the DOS Errorlevel variable after invoking ASCTO123 to detect
errors.

/wWK1File, specifies WK1File as the name of the 1-2-3 file that should be
created.  By default, ASCTO123 will use the same name as the input file,
with a WK1 extension.  ASCTO123 will always use a WK1 extension, no matter
what is specified using this option.

DOS ErrorLevel

On exit, ASCTO123 will set the DOS ErrorLevel variable as follows:

0 - Successful completion
250 - Invalid data error
251 - User cancellation of processing
252 - Invalid layout file or field descriptor
253 - Insufficient memory error
254 - File i/o error or insufficient handles
255 - Invalid command line syntax error

If you are invoking ASCTO123 from a batch file, you should test the
ErrorLevel variable for a value of 250 or more, and take appropriate action.

USER CANCELLATION

Once ASCTO123 starts writing the 1-2-3 file, it can be interrupted simply by
pressing a key.  You will be prompted whether to cancel processing or
continue.  This feature is sometimes useful if you are translating a very
large file into 1-2-3 format.  This is only available if you have not
disabled messages to the screen using the /v- command line switch.  In this
case you can use the Ctrl-brk key combination to interrupt the program.

LAYOUT FILE DESCRIPTION

The layout file is a text file which describes the length and format of all
fields within the ASCII text file which is to be converted to WK1 format.
Each line of the layout file consists of (up to) 5 descriptors which describe
the format of a single field.  The first 3 descriptors are required, the last
2 are optional.  Each line of the layout file looks like:

FieldName,FieldLength,FieldFormat,IncludeField,FieldOrder

where,

FieldName is just a label describing the contents of the field, for example,
BirthDate for a field containing birth dates.  The FieldName labels are
written to the first row of the WK1 file to serve as titles.  The FieldName
is a required item.

FieldLength is the length of the field in the input (ASCII) file.
FieldLength cannot exceed 240 for labels (as this is the maximum length of a
label in 1-2-3 v2.0).  If you have a large section of your file which will
not be included in the WK1 file, you may have to split it into 2 or more
separate fields, each of which is less that 240 characters in length, in
order to get ASCTO123 to process it.  The FieldLength is a required item.

FieldFormat is a string which describes the format, or data type, of the
field.  The string may be one of the following:

FieldFormat String    Data Type                1-2-3 Format

L            Label, or alphanumeric string        Label
N#            Number, with (optional) # decimal    Number
            places (implied or explicit)
Dpicture        Date, with 'picture' defining the    Date
            position of the day, month and
            year within the field.  See below.
Tpicture        Time, with 'picture' defining the    Time
            position of the hour, minute and
            second within the field.  'AM' or
            'PM' indicates the presence of the
            AM/PM in the field.  Otherwise, time
            values are interpreted in 24 hour
            format.

The FieldFormat is a required item.

IncludeField is either 'Y' (to include the field in the WK1 file), or 'N' to
exclude it.  Each field is included in the WK1 file by default.  Therefore,
the IncludeField is an optional item.

FieldOrder is an integer identifying the field order (from left to right)
that the field should occupy in the WK1 file.  By default, fields are written
to the WK1 file in the same order that they occur in the input file.
Therefore, this is an optional field.  You can use this item to re-order the
fields in the WK1 file.  If this item is used for one (included) field, then
it must be used for every (included) field.  Note that you must specify the
IncludeField item in order to specify the FieldOrder item.

'PICTURE STRINGS' FOR DATE FIELDS

The picture string for a date field specifies the position of the day, month
and year fields, if present, within the date field, as well as any other
special formatting characters that appear within the date field, such as
slash or hyphen characters.  The characters 'd', 'm', 'y' and 'yy' are used
to specify the locations of the 2-digit day, 2-digit month, 2-digit year and
4-digit year values within the date field.  For example, a (10 character)
date field containing dates of the form

1993-02-28

would have the following picture string:

Dyy-m-d

while a (5 character) date field with dates of the form

31/01

would have a picture string that looked like:

Dd/m

'PICTURE STRINGS' FOR TIME FIELDS

The picture string for a time field specifies the position of the hour,
minute and second values, if present, within the time field, as well as any
other special formatting characters that appear within the time field, such
as colon or hyphen characters.  The characters 'h', 'm', 's' are used to
specify the locations of the 2-digit hour, 2-digit minute and 2-digit second
values within the time field.  'AM' or 'PM' can be placed in the picture
string to indicate the presence of AM/PM within the time field.  If not
present, time values are interpreted in 24 hour format.  For example, the
hour value of 15 would be interpreted as 3:00PM.

For example, a (8 character) time field containing time values of the form

21:10:30

would have the following picture string:

Th:m:s

while a (7 character) time field with time values of the form

08:30AM

would have a picture string that looked like:

Th:mAM